package com.qp.basics.estivate.mysql;

import com.qp.basics.estivate.*;

import com.qp.basics.estivate.support.DriverConnectionProvider;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.*;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Vector;

/**
 * 针对MySql的数据库实现类。 现在只在MySQL 5.2.3-falcon-alpha-community-nt下做了测试。
 * 
 */
public class DatabaseMySql extends BaseDatabase {

	private static DataTypeConvertor dataTypeConvertor = new DataTypeConvertorMysql();
	private static final Log logger = LogFactory.getLog(DatabaseMySql.class);
	private String mysqlBinPath = "c:\\xampp\\mysql\\bin\\";
	// 字段定义表
	private static final String COLUMN_TABLE = "INFORMATION_SCHEMA.COLUMNS";
	// 表的定义表
	private static final String TABLE_TABLE = "INFORMATION_SCHEMA.TABLES";
	// 主键定义表
	private static final String PK_TABLE = "INFORMATION_SCHEMA.KEY_COLUMN_USAGE";

	public DatabaseMySql() {

	}

	public String getPostFix() {
		return "`";
	}

	public String getPreFix() {
		return "`";
	}

	public Table[] addCheckConstraints(Table[] tables) {

		return tables;
	}

	public Table[] getTables() {
		if (tables != null)
			return tables;
		String tableSelectSql = "select * from " + TABLE_TABLE
				+ " where TABLE_SCHEMA='" + this.getTableSchema() + "'";
		ResultSet rs;
		Vector v = new Vector();
		Connection conn = null;
		try {
			conn = this.getConnectionProvider().getConnection();
			rs = conn.createStatement().executeQuery(tableSelectSql);
			while (rs.next()) {
				Map properties = this.resultSetToMap(rs);
				Table table = new Table();
				table.setDatabase(this);
				table.putProperties(properties);
				table.setTableName(properties.get("TABLE_NAME").toString());
				if (properties.get("TABLE_COMMENT") != null) {
					String comment = properties.get("TABLE_COMMENT").toString();
					if (comment.indexOf(':') < 0) {
						table.setComment(comment);
					} else {
						if (comment.indexOf(';') > 0
								&& comment.indexOf(';') < comment.indexOf(':')) {
							comment = comment
									.substring(0, comment.indexOf(';'));
							table.setComment(comment);
						}
					}
				}
				table.setTableType(properties.get("TABLE_TYPE").toString());
				v.add(table);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
			}
		}
		tables = new Table[v.size()];
		v.toArray(tables);
		return tables;
	}

	public String getTableCreateSql(Table table) {
		StringBuffer createSql = new StringBuffer();
		createSql.append("CREATE TABLE `" + table.getTableName() + "` (");
		Column[] columns = table.getColumns();
		// `channel_name` VARCHAR( 20 ) DEFAULT 'ds' NOT NULL AUTO_INCREMENT
		// COMMENT '注释' ,
		for (int i = 0; i < columns.length; i++) {
			if (i != 0)
				createSql.append(',');
			createSql.append("\n");
			Column col = columns[i];
			String colName = col.getColumnName();
			String dataType = col.getDataType();
			Object defaultValue = col.getDefaultValue();
			String columnSize = col.getColumnSize();
			String comment = col.getComment();
			createSql.append("`" + colName + "` " + dataType);
			if (columnSize != null && !"".equals(columnSize)) {
				createSql.append("(" + columnSize + ") ");
			}
			if (defaultValue != null) {
				createSql.append(" DEFAULT '" + defaultValue + "'");
			}
			if (!col.isNullable()) {
				createSql.append(" NOT NULL ");
			}
			String EXTRA = col.getExtra();
			if (EXTRA != null) {
				createSql.append(" " + EXTRA + " ");
			}
			if (col.isIdAutoIncrement()) {
				if (EXTRA == null
						|| EXTRA.toLowerCase().indexOf("auto_increment") < 0) {
					createSql.append(" auto_increment ");
				}
			}
			if (comment != null && !"".equals(comment))
				createSql.append(" COMMENT '" + comment + "'");
		}
		Column[] pks = table.getPks();
		if (pks != null && pks.length > 0) {
			createSql.append(',');
			createSql.append("\n");
			createSql.append("PRIMARY KEY ( ");
			for (int i = 0; i < pks.length; i++) {
				if (i != 0)
					createSql.append(',');
				Column col = pks[i];
				createSql.append("`" + col.getColumnName() + "`");
			}
			createSql.append(')');
		}
		createSql.append("\n");
		createSql.append(")");
		if (table.getComment() != null) {
			createSql.append("COMMENT = '" + table.getComment() + "'");
		}
		return createSql.toString();
	}

	public Column[] getColumns(Table table) {
		String tableName = table.getTableName();
		String columnQuerySql = "select * from " + COLUMN_TABLE
				+ " where table_name='" + tableName + "' and table_schema='"
				+ this.getTableSchema() + "'";
		String pkQuerySql = "select * from " + PK_TABLE
				+ " where TABLE_SCHEMA='" + this.getTableSchema()
				+ "' and TABLE_NAME='" + table.getTableName()
				+ "' and CONSTRAINT_NAME='PRIMARY'";
		List pkNames = new ArrayList();
		Connection conn = this.getConnectionProvider().getConnection();
		try {
			ResultSet rsOfPks = conn.createStatement().executeQuery(pkQuerySql);
			while (rsOfPks.next()) {
				pkNames.add(rsOfPks.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + table.getTableName(), e);
		} finally {
			closeConn(conn);
		}
		conn = this.getConnectionProvider().getConnection();
		ResultSet rs = null;
		Vector v = new Vector();
		try {
			rs = conn.createStatement().executeQuery(columnQuerySql);
			while (rs.next()) {
				Map properties = this.resultSetToMap(rs);
				Column column = new Column();
				column.setTable(table);
				column.putProperties(properties);
				column.setColumnName(properties.get("COLUMN_NAME").toString());

				Object EXTRA = properties.get("EXTRA");
				if (EXTRA != null) {
					column.setExtra(EXTRA.toString());
					if ("auto_increment".equalsIgnoreCase(EXTRA.toString())) {
						column.setIdAutoIncrement(true);
					}
				}

				if (properties.get("COLUMN_COMMENT") != null)
					column.setComment(properties.get("COLUMN_COMMENT")
							.toString());
				if (properties.get("COLUMN_DEFAULT") != null)
					column.setDefaultValue(properties.get("COLUMN_DEFAULT")
							.toString());
				column.setNullable(("YES".equals(properties.get("IS_NULLABLE")
						.toString())));
				String columnType = properties.get("COLUMN_TYPE").toString();
				int braceIndex = columnType.indexOf('(');
				if (braceIndex > 0) {
					String dataType = columnType.substring(0, braceIndex);
					String columnSize = columnType.substring(braceIndex + 1,
							columnType.length() - 1);
					column.setDataType(dataType);
					column.setColumnSize(columnSize);
				} else {
					column.setDataType(columnType);

				}
				for (int i = 0; i < pkNames.size(); i++) {
					if (column.getColumnName().equalsIgnoreCase(
							pkNames.get(i).toString())) {
						column.setPk(true);
					}
				}
				v.add(column);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + " tableSchema="
					+ this.getTableSchema(), e);
		} finally {
			closeConn(conn);
		}
		Column[] columns = new Column[v.size()];
		v.toArray(columns);
		return columns;
	}

	public void alterColumn(Table table, String oldColumnName, Column column) {
		// ALTER TABLE `channel` CHANGE `channelId` `channelId` BIGINT( 20 )
		// DEFAULT '0' NOT NULL
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String alterColumnSql = "ALTER TABLE `"
				+ tableName
				+ "` CHANGE `"
				+ oldColumnName
				+ "` `"
				+ newColName
				+ "` "
				+ column.getDataType()
				+ (column.getColumnSize() == null
						|| "".equals(column.getColumnSize()) ? "" : "("
						+ column.getColumnSize() + ")")
				+ ((column.getDefaultValue() == null || "".equals(column
						.getDefaultValue())) ? "" : " DEFAULT '"
						+ column.getDefaultValue() + "'")
				+ (column.isNullable() ? "" : " NOT NULL ")
				+ (column.getExtra() == null ? "" : " " + column.getExtra()
						+ " ")
				+ (column.getComment() == null ? "" : " COMMENT '"
						+ column.getComment() + "'");

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void addColumn(Table table, Column column) {
		// ALTER TABLE table_name ADD field_name field_type;
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String extra = (String) column.getProperties().get("EXTRA");
		String alterColumnSql = "ALTER TABLE `"
				+ tableName
				+ "` ADD `"
				+ newColName
				+ "` "
				+ column.getDataType()
				+ (column.getColumnSize() == null
						|| "".equals(column.getColumnSize()) ? "" : "("
						+ column.getColumnSize() + ")")
				+ ((column.getDefaultValue() == null || "".equals(column
						.getDefaultValue())) ? "" : "DEFAULT "
						+ column.getDefaultValue())
				+ (column.isNullable() ? "" : " NOT NULL ")
				+ (extra != null ? extra : "")
				+ ((column.getComment() == null || "".equals(column
						.getComment())) ? "" : " COMMENT '"
						+ column.getComment() + "'");

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void dropColumn(Table table, Column column) {
		// ALTER TABLE table_name DROP field_name;
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String alterColumnSql = "ALTER TABLE `" + tableName + "` DROP `"
				+ newColName + "` ";

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void dropTable(String tableName) {
		String sql = "drop table `" + tableName + "`";
		executeUpdate(sql);
		this.tables = null;
	}

	public void resetPrimaryKey(Table table, Column[] pks) {
		// ALTER TABLE `channel` DROP PRIMARY KEY ,
		// ADD PRIMARY KEY ( `id` , `channel_name` )

		String sql = "ALTER TABLE `" + table.getTableName() + "`";
		Column[] oldPks = table.getPks();
		if (oldPks != null && oldPks.length > 0) {
			sql += " DROP PRIMARY KEY ";
		}
		if (pks != null && pks.length > 0) {
			if (oldPks != null && oldPks.length > 0) {
				sql += ",";
			}
			sql += " ADD PRIMARY KEY (";
			for (int i = 0; i < pks.length; i++) {
				if (i != 0)
					sql += ',';
				Column col = pks[i];
				sql += '`' + col.getColumnName() + '`';
			}
			sql += ")";
		}
		executeUpdate(sql);
		table.setColumns(this.getColumns(table));
	}

	public String getDatabaseType() {
		return "mysql";
	}

	public void alterTableComment(String tableName, String comment) {
		String sql = "alter table `" + tableName + "` comment='" + comment
				+ "'";
		this.executeUpdate(sql);
		Table table = this.getTable(tableName);
		table.setComment(comment);
	}

	public void alterTableName(String oldTableName, String tableName) {
		Table table = this.getTable(oldTableName);
		String sql = "alter table `" + oldTableName + "` rename to `"
				+ tableName + "`";
		this.executeUpdate(sql);
		table.setTableName(tableName);
	}

	private static final String[] dataTypes = new String[] { "varchar",
			"tinyint", "text", "date", "smallint", "mediumint", "int",
			"bigint", "float", "double", "decimal", "datetime", "timestamp",
			"time", "year", "char", "tinyblob", "tinytext", "blob",
			"mediumblob", "mediumtext", "longblob", "longtext", "enum", "set" };

	public String[] getDataTypes() {
		return dataTypes;
	}

	public Session openSession() {
		SessionMySql s = new SessionMySql();
		s.setDatabase(this);
		s.setConnection(this.connectionProvider.getConnection());
		return s;
	}

	@Override
	public void exportDatabase(String exportType, String exportTo) {
		try {
			Runtime rt = Runtime.getRuntime();

			// 调用 mysql 的 cmd:
			Process child = rt.exec(mysqlBinPath
					+ "mysqldump -u root --set-charset=utf8 "
					+ this.getTableSchema());// 设置导出编码为utf8。这里必须是utf8

			// 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行
			InputStream in = child.getInputStream();// 控制台的输出信息作为输入流
			InputStreamReader xx = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码
			// 组合控制台输出信息字符串
			BufferedReader br = new BufferedReader(xx);
			// 要用来做导入用的sql目标文件：
			FileOutputStream fout = new FileOutputStream(exportTo);
			OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");

			String inStr;
			while ((inStr = br.readLine()) != null) {
				writer.write(inStr + "\r\n");
				writer.flush();
			}

			// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免

			// 别忘记关闭输入输出流
			in.close();
			xx.close();
			br.close();
			writer.close();
			fout.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void importDatabase(String importType, String importFrom) {
		try {
			Runtime rt = Runtime.getRuntime();

			// 调用 mysql 的 cmd:
			Process child = rt.exec(mysqlBinPath + "mysql -u root "
					+ this.getTableSchema());
			OutputStream out = child.getOutputStream();// 控制台的输入信息作为输出流
			BufferedReader br = new BufferedReader(new InputStreamReader(
					new FileInputStream(importFrom), "utf8"));
			OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
			String inStr;
			while ((inStr = br.readLine()) != null) {
				writer.write(inStr);
			}

			// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
			writer.flush();
			// 别忘记关闭输入输出流
			out.close();
			br.close();
			writer.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<View> getUserViews() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getViewTextByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	public String getMysqlBinPath() {
		return mysqlBinPath;
	}

	public void setMysqlBinPath(String mysqlBinPath) {
		this.mysqlBinPath = mysqlBinPath;
	}

	public DataTypeConvertor getDataTypeConvertor() {
		return dataTypeConvertor;
	}

	@Override
	public List<Database> getAvailableDatabases() {

		List<Database> dbList = new ArrayList<Database>();

		String url = connectionProvider.getUrl();
		Connection conn = connectionProvider.getConnection();
		try {
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet rs = meta.getCatalogs();

			while (rs.next()) {
				String db = rs.getString("TABLE_CAT");
				
				String newUrl = url.replaceAll(getDatabaseName(), db);
				
				logger.info("Add a new db item ,url :"+newUrl);

				Database newDBItem = getDatabaseByType(getDatabaseType());
				newDBItem.setDatabaseName(db);
				newDBItem.setTableSchema(db);
				DriverConnectionProvider newConnectionProvider = new DriverConnectionProvider();
				newConnectionProvider.setDriver(connectionProvider.getDriver());
				newConnectionProvider.setPassword(connectionProvider
						.getPassword());
				newConnectionProvider.setUser(connectionProvider.getUser());
				newConnectionProvider.setUrl(newUrl);
				newDBItem.setConnectionProvider(newConnectionProvider);

				dbList.add(newDBItem);
			}
			rs.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return dbList;
	}
}
